package com.xinlang.excel.io;


import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.util.Date;
import java.util.Locale;

import javax.imageio.ImageIO;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelWriter {

	jxl.write.DateFormat dateFormat = new jxl.write.DateFormat("yyyy-MM-dd HH:mm:ss");
	jxl.write.WritableCellFormat writableCellDateFormat = new jxl.write.WritableCellFormat(
			dateFormat);
	jxl.write.NumberFormat numberFormat = new jxl.write.NumberFormat("#0");
	jxl.write.WritableCellFormat writableCellLongFormat = new jxl.write.WritableCellFormat(
			numberFormat);
	jxl.write.NumberFormat nfFLOAT = new jxl.write.NumberFormat("0.00");
	jxl.write.WritableCellFormat writableCellDoubleFormat = new jxl.write.WritableCellFormat(
			nfFLOAT);
	jxl.write.NumberFormat nfPERCENT = new jxl.write.NumberFormat("0.00%");
	jxl.write.WritableCellFormat writableCellPercentageFormat = new jxl.write.WritableCellFormat(
			nfPERCENT);
	jxl.write.WritableCellFormat cellFormat = new jxl.write.WritableCellFormat();

	WritableWorkbook workbook;
	WritableSheet excelSheet;
	String perName = "";
	// 由于jxl操作row有限制，目前暂时进行分页处理
	private static int RECORDSPERSHEET = 50000;

	public ExcelWriter(String inputFile) throws IOException {
		File file = new File(inputFile);
		WorkbookSettings wbSettings = new WorkbookSettings();
		wbSettings.setLocale(new Locale("en", "EN"));
		workbook = Workbook.createWorkbook(file, wbSettings);
		initCellFormat();
	}

	public void createSheet(String str) {
		excelSheet = workbook.getSheet(str);
		if (excelSheet == null) {
			int numberOfSheets = this.workbook.getNumberOfSheets();
			excelSheet = this.workbook.createSheet(str, numberOfSheets);
			perName = str.split("_")[0];
		}
	}

	public void initCellFormat() {
		/*
		 * try { cellFormat.setAlignment(jxl.format.Alignment.CENTRE); } catch
		 * (WriteException e) { e.printStackTrace(); }
		 */
	}

	public String getExcelSheetName() {
		return this.excelSheet.getName();
	}

	public void setExcelSheet(WritableSheet sheet) {
		this.excelSheet = sheet;
	}

	public WritableSheet getExcelSheet() {
		return this.excelSheet;
	}

	public WritableWorkbook getExcelWorkbook() {
		return this.workbook;
	}

	public void close() {
		try {
			if (null != workbook) {
				workbook.write();
				workbook.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void addMergeValue(Integer startColumn, Integer startRow, Integer endColumn,
			Integer endRow, String str) throws WriteException, RowsExceededException {
		if (startColumn > 0 && startRow % RECORDSPERSHEET == 0) {
			createSheet(perName + "_" + startRow / RECORDSPERSHEET);
		}
		jxl.write.Label label;
		Colour c = Colour.RED;
		jxl.write.WritableCellFormat mergeFormat = new jxl.write.WritableCellFormat();
		mergeFormat.setBackground(c);
		mergeFormat.setAlignment(jxl.format.Alignment.CENTRE);
		label = new jxl.write.Label(startColumn, startRow % RECORDSPERSHEET, str, mergeFormat);
		this.excelSheet.addCell(label);
		this.excelSheet.mergeCells(startColumn, startRow, endColumn, endRow);
	}

	public void addValue(Integer column, Integer row, String str) throws WriteException,
			RowsExceededException {
		if (row > 0 && row % RECORDSPERSHEET == 0) {
			createSheet(perName + "_" + row / RECORDSPERSHEET);
		}
		jxl.write.Label label;
		label = new jxl.write.Label(column, row % RECORDSPERSHEET, str, cellFormat);
		this.excelSheet.addCell(label);
	}

	public void addColorValue(Integer column, Integer row, String str) throws WriteException,
			RowsExceededException {
		if (row > 0 && row % RECORDSPERSHEET == 0) {
			createSheet(perName + "_" + row / RECORDSPERSHEET);
		}
		jxl.write.Label label;
		Colour c = Colour.GRAY_25;
		jxl.write.WritableCellFormat colorFormat = new jxl.write.WritableCellFormat();
		colorFormat.setBackground(c);
		label = new jxl.write.Label(column, row % RECORDSPERSHEET, str, colorFormat);
		this.excelSheet.addCell(label);
	}

	public void addValue(Integer column, Integer row, ExcelFormula excelFormula) throws WriteException,
			RowsExceededException {
		if (row > 0 && row % RECORDSPERSHEET == 0) {
			createSheet(perName + "_" + row / RECORDSPERSHEET);
		}
		jxl.write.Formula formula;
		formula = new jxl.write.Formula(column, row % RECORDSPERSHEET, excelFormula.getExpression());
		this.excelSheet.addCell(formula);
	}

	public void addValue(Integer column, Integer row, Long longNum) throws WriteException,
			RowsExceededException {
		if (row > 0 && row % RECORDSPERSHEET == 0) {
			createSheet(perName + "_" + row / RECORDSPERSHEET);
		}
		jxl.write.Number number;
		number = new jxl.write.Number(column, row % RECORDSPERSHEET, longNum,
				writableCellLongFormat);
		this.excelSheet.addCell(number);
	}

	public void addValue(Integer column, Integer row, Double doubleNum) throws WriteException,
			RowsExceededException {
		if (row > 0 && row % RECORDSPERSHEET == 0) {
			createSheet(perName + "_" + row / RECORDSPERSHEET);
		}
		jxl.write.Number number;
		number = new jxl.write.Number(column, row % RECORDSPERSHEET, doubleNum,
				writableCellDoubleFormat);
		this.excelSheet.addCell(number);
	}

	public void addPercentage(Integer column, Integer row, Double doubleNum) throws WriteException,
			RowsExceededException {
		if (row > 0 && row % RECORDSPERSHEET == 0) {
			createSheet(perName + "_" + row / RECORDSPERSHEET);
		}
		jxl.write.Number number;
		number = new jxl.write.Number(column, row % RECORDSPERSHEET, doubleNum / 100,
				writableCellPercentageFormat);
		this.excelSheet.addCell(number);
	}

	public void addValue(Integer column, Integer row, Integer intNum) throws WriteException,
			RowsExceededException {
		if (row > 0 && row % RECORDSPERSHEET == 0) {
			createSheet(perName + "_" + row / RECORDSPERSHEET);
		}
		jxl.write.Number number;
		number = new jxl.write.Number(column, row % RECORDSPERSHEET, intNum);
		this.excelSheet.addCell(number);
	}

	public void addValue(Integer column, Integer row, Date dateTime) throws WriteException,
			RowsExceededException {
		if (row > 0 && row % RECORDSPERSHEET == 0) {
			createSheet(perName + "_" + row / RECORDSPERSHEET);
		}
		jxl.write.DateTime date = new jxl.write.DateTime(column, row % RECORDSPERSHEET, dateTime,
				writableCellDateFormat);
		this.excelSheet.addCell(date);
	}

	public void addValue(Integer column, Integer row, Boolean bool) throws RowsExceededException,
			WriteException {
		if (row > 0 && row % RECORDSPERSHEET == 0) {
			createSheet(perName + "_" + row / RECORDSPERSHEET);
		}

		jxl.write.Boolean booleanCell = new jxl.write.Boolean(column, row % RECORDSPERSHEET, bool);
		this.excelSheet.addCell(booleanCell);
	}

	public void addPicture(int columnNum, int rowNum, WritableSheet picSheet, BufferedImage picImage)
			throws Exception {
		// 开始位置
		double picBeginCol = columnNum * 1.0;
		double picBeginRow = rowNum * 1.0;
		// 图片时间的高度，宽度
		double picCellWidth = 0.0;
		double picCellHeight = 0.0;
		// 读入图片
		// BufferedImage picImage = ImageIO.read(pictureFile);
		// 取得图片的像素高度，宽度
		int picWidth = picImage.getWidth();
		int picHeight = picImage.getHeight();

		// 计算图片的实际宽度
		int picWidthT = picWidth * 32; // 具体的实验值，原理不清楚。
		for (int x = 0; x < 1234; x++) {
			int bc = (int) Math.floor(picBeginCol + x);
			// 得到单元格的宽度
			int v = picSheet.getColumnView(bc).getSize();
			double offsetT = 0.0;
			if (0 == x)
				offsetT = (picBeginCol - bc) * v;
			if (0.0 + offsetT + picWidthT > v) {
				// 剩余宽度超过一个单元格的宽度
				double ratioT = 1.0;
				if (0 == x) {
					ratioT = (0.0 + v - offsetT) / v;
				}
				picCellWidth += ratioT;
				picWidthT -= (int) (0.0 + v - offsetT);
			} else { // 剩余宽度不足一个单元格的宽度
				double ratioR = 0.0;
				if (v != 0)
					ratioR = (0.0 + picWidthT) / v;
				picCellWidth += ratioR;
				break;
			}
		}
		// 计算图片的实际高度
		int picHeightT = picHeight * 15;
		for (int x = 0; x < 1234; x++) {
			int bc = (int) Math.floor(picBeginRow + x);
			// 得到单元格的高度
			int v = picSheet.getRowView(bc).getSize();
			double offsetR = 0.0;
			if (0 == x)
				offsetR = (picBeginRow - bc) * v;
			if (0.0 + offsetR + picHeightT > v) {
				// 剩余高度超过一个单元格的高度
				double ratioQ = 1.0;
				if (0 == x)
					ratioQ = (0.0 + v - offsetR) / v;
				picCellHeight += ratioQ;
				picHeightT -= (int) (0.0 + v - offsetR);
			} else {// 剩余高度不足一个单元格的高度
				double ratioM = 0.0;
				if (v != 0)
					ratioM = (0.0 + picHeightT) / v;
				picCellHeight += ratioM;
				break;
			}
		}
		// 生成一个图片对象。
		// 处理图片文件，以便产生ByteArray
		ByteArrayOutputStream handlePicture = new ByteArrayOutputStream();
		ImageIO.write(picImage, "png", handlePicture);
		WritableImage image = new WritableImage(picBeginCol, picBeginRow, picCellWidth,
				picCellHeight, handlePicture.toByteArray());
		// 把图片插入到sheet
		picSheet.addImage(image);
	}

	public static void main(String[] args) throws Exception {
	}
}